import oracle.jdbc.pool.OracleDataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

/**
 * Created by IntelliJ IDEA.
 * User: MrN
 * Date: 21.11.11
 * Time: 20:35
 * To change this template use File | Settings | File Templates.
 */
public class SqlBot
{

    //Test Vitaly
    // Test Aleks



    private OracleDataSource ods;
    private Connection conn;
    private Statement stmt;
    private PreparedStatement ps;
    private RSSReaderDOM parser;

    static final String connect_string = "jdbc:oracle:thin:Gruppe1/Gruppe12011@//pauro.hin.no:1521/orcl";

    public SqlBot() throws ClassNotFoundException, SQLException
    {
        parser = new RSSReaderDOM();
        Class.forName("oracle.jdbc.OracleDriver");
        System.out.println("Driver for Oracle loaded OK");

        // Create a OracleDataSource instance and set URL
        ods = new OracleDataSource();
        ods.setURL(connect_string);

        conn = ods.getConnection();
    }

    /* sqlBot bot2 = new sqlBot("Gruppe1", "Gruppe12011", "pauro.hin.no", 1521); */
    public SqlBot(String userName, String passwd, String url, int port) throws ClassNotFoundException, SQLException
    {
        parser = new RSSReaderDOM();
        String connect = "jdbc:oracle:thin:" + userName + "/" + passwd + "@" + url + ":" + port + "/orcl";
        Class.forName("oracle.jdbc.OracleDriver");
        System.out.println("Driver for Oracle loaded OK");

        // Create a OracleDataSource instance and set URL
        ods = new OracleDataSource();
        ods.setURL(connect);

        conn = ods.getConnection();
    }

    public ArrayList<String> getURL() throws ClassNotFoundException, SQLException
    {
        ArrayList<String> urls = new ArrayList<String>();
        conn = ods.getConnection();

        conn.setAutoCommit(false);
        stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select URL from NyhetsByrå");
        while (rset.next())
        {
          urls.add(rset.getString(1));
        }
        return urls;
    }

    public void addNews() throws ClassNotFoundException, SQLException
    {
        ArrayList<News> listOfNews;


        ArrayList<String> urls =  getURL();

        for(String value : urls)
        {
               listOfNews = parser.getNews(value);
               for(News news : listOfNews)
               {
                   addSingleNews(news, value);
               }
        }
    }
    private void addSingleNews(News value, String baseUrl) throws ClassNotFoundException, SQLException
    {
        stmt = conn.createStatement();
        PreparedStatement statement = null;
        String SQL_INSERT = "INSERT INTO NYHET VALUES(?, ?, ?, ?, ?, ?, ?)";

        statement = conn.prepareStatement(SQL_INSERT);
        conn.setAutoCommit(true);


        int id = getMaxID("Nyhet");
        if(id >= 0)
        {
            id++;
        }

        String url = value.getUrl();
        String title = value.getTitle();
        String description = value.getDescription();
        String pubDate = value.getDate();

        int firmID = getFirmID(baseUrl);
        int newsType = getNewsType(firmID);

        statement.setInt(1, id);
        statement.setString(2, url);
        statement.setString(3, title);
        statement.setString(4, description);
        statement.setString(5, pubDate);
        statement.setInt(6, firmID);
        statement.setInt(7, newsType);
        statement.executeUpdate();
    }

    public int getMaxID(String value) throws ClassNotFoundException, SQLException
    {
        stmt = conn.createStatement();
        int result = 1;
        String query = "SELECT MAX(id) FROM " + value;
        ResultSet getID = stmt.executeQuery(query);

        while(getID.next())
        {
            try
            {
                result = getID.getInt(1);
            } catch(NullPointerException e) { System.out.println("Shit gone wrong bro"); }
        }
        return result;
    }

    // Throws an SQL syntax error.
    private int getFirmID(String value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT ID FROM NYHETSBYRÅ WHERE URL = '" + value + "'";
        ResultSet FirmID = stmt.executeQuery(query);
        int result = 0;
        while(FirmID.next())
        {
            result = FirmID.getInt(1);
        }
        return result;
    }

    private int getNewsType(int value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT Type FROM NYHETSBYRÅ WHERE ID = '" + value + "'";
        ResultSet NewsType = stmt.executeQuery(query);
        int result = 0;
        while(NewsType.next())
        {
            result = NewsType.getInt(1);
        }
        return result;
    }
    private int getNYHETSTYPEID(String value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT ID FROM NYHETSTYPE WHERE BESKRIVELSE = '" + value + "'";
        ResultSet NewsType = stmt.executeQuery(query);
        int result = 0;
        while(NewsType.next())
        {
            result = NewsType.getInt(1);
        }
        return result;
    }

    public void addFeed(String name, String type, String url ) throws ClassNotFoundException, SQLException
    {

        stmt = conn.createStatement();
        PreparedStatement statement = null;

        String query = "INSERT INTO NYHETSBYRÅ VALUES(?,?,?,?,?,?,?)";
        int id = getMaxID("Nyhetsbyrå");
        System.out.println(id);
        if(id >= 0)
        {
            id++;
        }
        String date = now("dd.mm.yyyy");
        int typeNr = getNYHETSTYPEID(type);

        if(!checkNewsType(type))
        {
            addNewsType(type);
        }

        statement.setInt(1, id);
        statement.setString(2, name);
        statement.setInt(3, 5);
        statement.setString(4, date);
        statement.setInt(5, typeNr);
        statement.setString(6, now("dd.mm.yyyy"));
        statement.setString(7, url);


        statement = conn.prepareStatement(query);
        conn.setAutoCommit(true);

    }

    public void addNewsType(String value) throws ClassNotFoundException, SQLException
    {

            String desc = value;
            int id = getMaxID("NyhetsType");

            stmt = conn.createStatement();
            PreparedStatement statement = null;

            String query = "INSERT INTO NYHETSTYPE VALUES(?,?)";

            statement = conn.prepareStatement(query);
            conn.setAutoCommit(true);

            statement.setInt(1, (id+1));
            statement.setString(2, desc);
            statement.executeUpdate();

    }

    public boolean checkNewsType(String value) throws ClassNotFoundException, SQLException
    {
        String query = "SELECT ID FROM NYHETSTYPE WHERE BESKRIVELSE = '" + value + "'";
        stmt = conn.createStatement();
        ResultSet getNewsType = stmt.executeQuery(query);
        boolean result = false;

        if(getNewsType.next())
        {
            result = true;

        }
        return result;
    }

    public static String now(String dateFormat)
        {
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
            return sdf.format(cal.getTime());
        }

}




